package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.core.util.StringUtil;
import com.tianqu.tms.dto.AucManagerDto;
import com.tianqu.tms.entity.CarStyle;
import com.tianqu.tms.entity.Codes;

public class AucManagerDaoImp extends ViewListDao implements AucManagerDao {

	protected final String[] sortKeyList = new String[]{"ID","SERIAL","CARSTYLEID","MILEAGE","REGDATE","DISPLACEMENT","GEARBOX","FINALEXPETPRICES","BIDTIME","HIS.BIDTIME"};//成交车辆 

	protected AucManagerDaoImp(int pageNo, int maxIndex, int sortKey, 
			boolean asc) throws TmsDaoException {
		super(pageNo, maxIndex, sortKey, asc);
	}
	public AucManagerDaoImp() throws TmsDaoException{
		super();
	}
	/**
	 * 插入车辆竞价信息
	 */
	@Override
	public void insertAucManger(AucManagerDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append("    INSERT INTO     ");
		sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
		sql.append("  (        ");
		sql.append("	SERIAL,");
		sql.append("	STARTTIME,");
		sql.append("	AUCTIONDEADLINE,");
		sql.append("	BIDTYPE,");
		sql.append("	BIDSTATUS");

		sql.append(")VALUES(");
		sql.append("?,?,?,?,?)");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getSerial());
			ps.setTimestamp(2, dto.getStartTime());
			ps.setInt(3, dto.getAuctionDeadline());
			ps.setInt(4, dto.getBidType());
			ps.setInt(5, dto.getBidStatus());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
	}
 
	/**
	 * 更新竞价车辆信息
	 */
	@Override
	public void updateAucManager(AucManagerDto dto) throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("    UPDATE    ");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			sql.append("  SET        ");
			sql.append("	STARTTIME=?,");
			sql.append("	AUCTIONDEADLINE=?");
			sql.append(" WHERE ");
			sql.append("	ID=?");
			ps = conn.prepareStatement(sql.toString());

			ps.setTimestamp(1, dto.getStartTime());
			ps.setInt(2, dto.getAuctionDeadline());
			ps.setInt(3, dto.getId());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
	}

	/**
	 * 
	 * 根据车辆竞价状态查询竞价车辆信息
	 */
	@Override
	public List<AucManagerDto> findAucManageInfoByStatus(int status)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		AucManagerDto dto = null;
		List<AucManagerDto> list = new ArrayList<AucManagerDto>();

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("ID,");
			sql.append("SERIAL,");
			sql.append("FINALEXPETPRICES,");
			sql.append("MEMBERID,");
			sql.append("STARTTIME,");
			sql.append("ENDTIME,");
			sql.append("AUCTIONDEADLINE,");
			sql.append("BIDTYPE,     ");
			sql.append("BIDSTATUS,     ");
			sql.append("BIDTYPE     ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			sql.append("  WHERE bidStatus = " + status + " ");

			ps = conn.prepareStatement(sql.toString());

			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new AucManagerDto();
				dto.setId(rs.getInt("ID"));
				dto.setSerial(rs.getString("SERIAL"));
				dto.setFinalExpetPrices(rs.getString("FINALEXPETPRICES"));
				dto.setMemberId(rs.getString("MEMBERID"));
				dto.setStartTime(rs.getTimestamp("STARTTIME"));
				dto.setEndTime(rs.getTimestamp("ENDTIME"));
				dto.setAuctionDeadline(rs.getInt("AUCTIONDEADLINE"));
				dto.setBidType(rs.getInt("BIDTYPE"));
				dto.setBidStatus(rs.getInt("BIDSTATUS"));
				dto.setBidType(rs.getInt("BIDTYPE"));

				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return list;
	}

	/**
	 * 
	 * 根据时间段查询车辆竞价信息
	 */
	@Override
	public List<AucManagerDto> findAucInfoByPeriod(Timestamp startTime) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		AucManagerDto dto = null;
		List<AucManagerDto> list = new ArrayList<AucManagerDto>();
      System.out.println(startTime);
		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("ID,");
			sql.append("SERIAL,");
			sql.append("FINALEXPETPRICES,");
			sql.append("MEMBERID,");
			sql.append("STARTTIME,");
			sql.append("ENDTIME,");
			sql.append("AUCTIONDEADLINE,");
			sql.append("BIDTYPE,     ");
			sql.append("BIDSTATUS,     ");
			sql.append("BIDTYPE     ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			sql.append("  WHERE STARTTIME = '" + startTime + "' ");
			sql.append(" AND ");
			sql.append("BIDSTATUS="+'1');
			System.out.println("TMS_AUC_MANAGEMENT====="+sql);
			ps = conn.prepareStatement(sql.toString());
           
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new AucManagerDto();
				dto.setId(rs.getInt("ID"));
				dto.setSerial(rs.getString("SERIAL"));
				dto.setFinalExpetPrices(rs.getString("FINALEXPETPRICES"));
				dto.setMemberId(rs.getString("MEMBERID"));
				dto.setStartTime(rs.getTimestamp("STARTTIME"));
				dto.setEndTime(rs.getTimestamp("ENDTIME"));
				dto.setAuctionDeadline(rs.getInt("AUCTIONDEADLINE"));
				dto.setBidType(rs.getInt("BIDTYPE"));
				dto.setBidStatus(rs.getInt("BIDSTATUS"));
				dto.setBidType(rs.getInt("BIDTYPE"));

				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return list;
	}

	/**
	 * 待竞价列表
	 */
	@Override
	public List<AucManagerDto> findAucInfoByStatus(int status) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		AucManagerDto dto = null;
		List<AucManagerDto> list = new ArrayList<AucManagerDto>();

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("ID,");
			sql.append("SERIAL,");
			sql.append("FINALEXPETPRICES,");
			sql.append("MEMBERID,");
			sql.append("STARTTIME,");
			sql.append("ENDTIME,");
			sql.append("AUCTIONDEADLINE,");
			sql.append("BIDTYPE,     ");
			sql.append("BIDSTATUS,     ");
			sql.append("BIDTYPE     ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			sql.append("  WHERE BIDSTATUS = " + status + " ");
			sql.append("  AND SUBSTRING(STARTTIME,1,10)=SUBSTRING(SYSDATE(),1,10) ");

			ps = conn.prepareStatement(sql.toString());

			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new AucManagerDto();
				dto.setId(rs.getInt("ID"));
				dto.setSerial(rs.getString("SERIAL"));
				dto.setFinalExpetPrices(rs.getString("FINALEXPETPRICES"));
				dto.setMemberId(rs.getString("MEMBERID"));
				dto.setStartTime(rs.getTimestamp("STARTTIME"));
				dto.setEndTime(rs.getTimestamp("ENDTIME"));
				dto.setAuctionDeadline(rs.getInt("AUCTIONDEADLINE"));
				dto.setBidType(rs.getInt("BIDTYPE"));
				dto.setBidStatus(rs.getInt("BIDSTATUS"));
				dto.setBidType(rs.getInt("BIDTYPE"));

				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return list;
	}

	/**
	 * 根据车辆ID查询车辆竞价信息
	 * 
	 * 今天开始的车辆信息
	 * 
	 */
	@Override
	public AucManagerDto getAucInfoById(int id)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		AucManagerDto dto = null;

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("ID,");
			sql.append("SERIAL,");
			sql.append("FINALEXPETPRICES,");
			sql.append("MEMBERID,");
			sql.append("DONEPRICES,");
			sql.append("DONEMEMBERID,");
			sql.append("EXPETPRICESNUM,");
			sql.append("STARTTIME,");
			sql.append("ENDTIME,");
			sql.append("AUCTIONDEADLINE,");
			sql.append("BIDSTATUS,     ");
			sql.append("BIDTYPE     ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			sql.append("  WHERE ID = '" + String.valueOf(id) + "' ");

			ps = conn.prepareStatement(sql.toString());

			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new AucManagerDto();
				dto.setId(rs.getInt("ID"));
				dto.setSerial(rs.getString("SERIAL"));
				dto.setFinalExpetPrices(rs.getString("FINALEXPETPRICES"));
				dto.setMemberId(rs.getString("MEMBERID"));
				dto.setDonePrices(rs.getInt("DONEPRICES"));
				dto.setDoneMemberId(rs.getString("DONEMEMBERID"));
				dto.setExpetPricesNum(rs.getInt("EXPETPRICESNUM"));
				dto.setStartTime(rs.getTimestamp("STARTTIME"));
				dto.setEndTime(rs.getTimestamp("ENDTIME"));
				dto.setAuctionDeadline(rs.getInt("AUCTIONDEADLINE"));
				dto.setBidType(rs.getInt("BIDTYPE"));
				dto.setBidStatus(rs.getInt("BIDSTATUS"));
				dto.setBidType(rs.getInt("BIDTYPE"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return dto;
	}

// 竞价管理修正 Start
	/**
	 * 根据车辆ID查询车辆竞价信息
	 * 
	 * 今天开始的车辆信息
	 * 
	 */
	@Override
	public AucManagerDto getAucInfoBySerialAll(int id)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		AucManagerDto dto = null;

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("ID,");
			sql.append("SERIAL,");
			sql.append("BIDSTATUS,");
			sql.append("BIDTYPE,");
			sql.append("DONEPRICES,");
			sql.append("DONEMEMBERID,");
			sql.append("FINALEXPETPRICES,");
			sql.append("MEMBERID,");
			sql.append("EXPETPRICESNUM,");
			sql.append("STARTTIME,");
			sql.append("AUCTIONDEADLINE	");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			sql.append("  WHERE ID = '" + String.valueOf(id) + "' ");

			ps = conn.prepareStatement(sql.toString());
			// 执行sql
			rs = ps.executeQuery();
			while (rs.next()) {
				dto = new AucManagerDto();
				dto.setId(rs.getInt("ID"));
				dto.setSerial(rs.getString("SERIAL"));
				dto.setBidStatus(rs.getInt("BIDSTATUS"));
				dto.setBidType(rs.getInt("BIDTYPE"));
				dto.setDonePrices(rs.getInt("DONEPRICES"));
				dto.setDoneMemberId(rs.getString("DONEMEMBERID"));
				dto.setFinalExpetPrices(rs.getString("FINALEXPETPRICES"));
				dto.setMemberId(rs.getString("MEMBERID"));
				dto.setExpetPricesNum(rs.getInt("EXPETPRICESNUM"));
				dto.setStartTime(rs.getTimestamp("STARTTIME"));
				dto.setAuctionDeadline(rs.getInt("AUCTIONDEADLINE"));

			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return dto;
	}
// 竞价管理修正 End

	/**
	 * 更新竞价状态
	 */
	@Override
	public void updateBidType(int bidType, int id)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE ");
		sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
		sql.append(" SET ");
		sql.append(" BIDSTATUS=?,  ");
		sql.append(" UPDATETIME=SYSDATE()  ");
		sql.append(" WHERE ");
		sql.append(" ID=?");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, bidType);
			ps.setInt(2, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
	}

	/**
	 * 获得当前场次时间段内所有的竞价车辆信息
	 * @param startTime
	 * @param endTime
	 * @return
	 * @throws TmsDaoException
	 */
	@Override
	public List<AucManagerDto> getAucManagementList() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<AucManagerDto> list = new ArrayList<AucManagerDto>();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" * ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
		sql.append(" WHERE ");
		sql.append("bidType = 1 AND ");
		sql.append("SUBSTRING(startTime,1,10)=SUBSTRING(SYSDATE(),1,10)");
		sql.append(" ORDER BY endtime DESC,startTime DESC");
		
		try{
		ps=conn.prepareStatement(sql.toString());
		
		rs=ps.executeQuery();
		int i=0;
		while (rs.next()) {
			AucManagerDto dto = new AucManagerDto();
			dto.setId(rs.getInt("ID"));
			dto.setSerial(rs.getString("SERIAL"));
			dto.setFinalExpetPrices(rs.getString("FINALEXPETPRICES"));
			dto.setMemberId(rs.getString("MEMBERID"));
			dto.setDonePrices(rs.getInt("DONEPRICES"));
			dto.setDoneMemberId(rs.getString("DONEMEMBERID"));
			dto.setExpetPricesNum(rs.getInt("EXPETPRICESNUM"));
			dto.setStartTime(rs.getTimestamp("STARTTIME"));
			dto.setEndTime(rs.getTimestamp("ENDTIME"));
			dto.setAuctionDeadline(rs.getInt("AUCTIONDEADLINE"));
			dto.setBidType(rs.getInt("BIDTYPE"));
			dto.setBidStatus(rs.getInt("BIDSTATUS"));
			if(i%2 == 0){
				dto.setCssFlag(1);
			}else{
				dto.setCssFlag(2);
			}
			i++;
		 list.add(dto);
		}
		return list;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
	}

	
	/**
	 * 获得当前场次时间段内待竞价及竞价中的车辆信息
	 * @param startTime
	 * @param endTime
	 * @return
	 * @throws TmsDaoException
	 */
	@Override
	public List<AucManagerDto> getBidWeitList(String startTime,
			String endTime) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<AucManagerDto> list = new ArrayList<AucManagerDto>();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" * ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
		sql.append(" WHERE ");
		sql.append(" STARTTIME>=? ");
		sql.append(" AND  ");
		sql.append("STARTTIME<=? ");
		sql.append("AND (BIDSTATUS=1 OR BIDSTATUS=2) ");
		sql.append(" ORDER BY SERIAL+0 ASC");
		try{
		ps=conn.prepareStatement(sql.toString());
		ps.setString(1, startTime);
		ps.setString(2, endTime);
		rs=ps.executeQuery();
		while (rs.next()) {
			AucManagerDto dto = new AucManagerDto();
			dto.setId(rs.getInt("ID"));
			dto.setSerial(rs.getString("SERIAL"));
			dto.setFinalExpetPrices(rs.getString("FINALEXPETPRICES"));
			dto.setMemberId(rs.getString("MEMBERID"));
			dto.setStartTime(rs.getTimestamp("STARTTIME"));
			dto.setEndTime(rs.getTimestamp("ENDTIME"));
			dto.setAuctionDeadline(rs.getInt("AUCTIONDEADLINE"));
			dto.setBidType(rs.getInt("BIDTYPE"));
			dto.setBidStatus(rs.getInt("BIDSTATUS"));
			dto.setBidType(rs.getInt("BIDTYPE"));
		 list.add(dto);
		}
		return list;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
	}
	
	/**
	 * 查询所有成交车辆信息
	 */
	@Override
	public List getAllAucDealInfo(int memberId) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List list = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ");
		sql.append("MANAGE.ID AS ID,");
		sql.append("MANAGE.SERIAL AS SERIAL,");
		sql.append("AUC.CARSTYLEID AS CARSTYLEID,");//款式ID 取carName
		sql.append("AUC.MILEAGE AS MILEAGE,");//表显里程
		sql.append("AUC.REGDATE AS REGDATE,");//登记日期
		sql.append("AUC.DISPLACEMENT AS DISPLACEMENT,");//排气量
		sql.append("AUC.GEARBOX AS GEARBOX,");//变速箱
		sql.append("MANAGE.FINALEXPETPRICES AS FINALEXPETPRICES,");//成交价
		sql.append("HIS.BIDTIME AS BIDTIME ");//成交时间 最后一次出价的时间
		sql.append("FROM     ");
		sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
		sql.append("   AS MANAGE     ");
		sql.append("LEFT JOIN     ");
		sql.append(TmsTableNames.TMS_AUC_CAR_INFO);
		sql.append("   AS AUC      ");
		sql.append("ON AUC.SERIAL = MANAGE.SERIAL   ");
		sql.append("LEFT JOIN     ");
		sql.append(TmsTableNames.TMS_AUC_BIDHISTORY);
		sql.append("   AS HIS      ");
		sql.append("ON HIS.AUCMAGID = MANAGE.ID AND HIS.PRICE=MANAGE.FINALEXPETPRICES   ");
		sql.append("WHERE     ");
		sql.append(" MANAGE.BIDSTATUS = 3     ");
		sql.append(" AND HIS.MEMBERID = "+memberId+"     ");
		
		//加排序条件
		sql.append(getOrderBySql(sortKeyList));
		try{
			
		ps=conn.prepareStatement(getSql(sql.toString()));
		
		CarStyle style = CarStyle.getInstance();
		Codes code = Codes.getInstance();
		
		rs=ps.executeQuery();
		int i=0;
		while (rs.next()) {
			AucManagerDto dto = new AucManagerDto();
			dto.setId(rs.getInt("ID"));
			dto.setSerial(rs.getString("SERIAL"));
			dto.setCarStyleId(style.getCarStyleRealNameById(rs.getString("CARSTYLEID")));
			dto.setMileage(StringUtil.getString(rs.getInt("MILEAGE")));
			dto.setRegDate(rs.getString("REGDATE"));
			dto.setDisplacement(StringUtil.getNewExhaust(rs.getInt("DISPLACEMENT")));
			dto.setGearbox(code.getItemValueByItemIdAndItemCode(Codes.GEARBOX_TYPE, rs.getInt("GEARBOX")));
			dto.setFinalExpetPrices(StringUtil.transYtoW(rs.getInt("FINALEXPETPRICES")));
			dto.setBidTime(rs.getString("BIDTIME").substring(0, rs.getString("BIDTIME").length()-2)); 
			if(i%2 == 0){
				dto.setCssFlag(1);
			}else{
				dto.setCssFlag(2);
			}
			int num = (i+1)+(getPageNo()-1)*10;
			dto.setNum(num);
			i++;
			list.add(dto);
		}
		return list;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
	}
	
	// 竞价管理修正 Start
	/**
	 * 更新 成交商户 成交价格
	 */
	@Override
	public void updateAucManageInfoById(AucManagerDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE ");
		sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
		sql.append(" SET ");
		sql.append("BIDSTATUS=?,		");
		sql.append("DONEPRICES=?,		");
		sql.append("DONEMEMBERID=? 			");	
		sql.append(" WHERE ");
		sql.append(" ID=?");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, dto.getBidStatus());
			ps.setInt(2, dto.getDonePrices());
			ps.setString(3, dto.getDoneMemberId());
			ps.setInt(4, dto.getId());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}		
	}
	// 竞价管理修正 End

	/**
	 * 更新车辆竞价状态
	 */
	@Override
	public void upAucManageStatusBySerial(String serial, int bidStatus)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE ");
		sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
		sql.append(" SET ");
		sql.append(" UPDATETIME=SYSDATE(), 	");	
		sql.append(" BIDSTATUS="+bidStatus+"  ");	
		sql.append(" WHERE   ");
		sql.append(" SERIAL='"+serial+"'  ");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}		
	}
	
	//批量更新竞价信息
		@Override
		public void setCarbidInfo(AucManagerDto dto) throws TmsDaoException {
			PreparedStatement ps = null;
			ResultSet rs = null;
			StringBuffer sql = new StringBuffer();
			sql.append("UPDATE ");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			sql.append(" SET ");
			sql.append(" FINALEXPETPRICES=?,");	
			sql.append(" MEMBERID=?,");	
			sql.append(" BIDSTATUS=?, ");
			sql.append("ENDTIME=SYSDATE()");
			sql.append(" WHERE   ");
			sql.append(" ID=? ");
			try {
					ps = conn.prepareStatement(sql.toString());
					ps.setString(1, dto.getFinalExpetPrices());
					ps.setString(2, dto.getMemberId());
					ps.setInt(3, dto.getBidStatus());
					ps.setInt(4, dto.getId());
					ps.executeUpdate();

			} catch (SQLException e) {
				throw new TmsDaoException(e.toString());

			} finally {
				close(rs, ps);
			}		
		}
	/**
	 * 根据车辆ID和会员id查询车辆基本信息
	 */
	@Override
	public AucManagerDto getMyDealAucCarInfo(String serial, int memberID)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		AucManagerDto dto = null;

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("ID,");
			sql.append("SERIAL,");
			sql.append("FINALEXPETPRICES,");
			sql.append("MEMBERID,");
			sql.append("STARTTIME,");
			sql.append("ENDTIME,");
			sql.append("AUCTIONDEADLINE,");
			sql.append("BIDTYPE,     ");
			sql.append("BIDSTATUS,     ");
			sql.append("BIDTYPE     ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			sql.append("  WHERE SERIAL = '" + serial + "' ");
			sql.append("  AND MEMBERID = '" + memberID + "' ");

			ps = conn.prepareStatement(sql.toString());

			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new AucManagerDto();
				dto.setId(rs.getInt("ID"));
				dto.setSerial(rs.getString("SERIAL"));
				dto.setFinalExpetPrices(rs.getString("FINALEXPETPRICES"));
				dto.setMemberId(rs.getString("MEMBERID"));
				dto.setStartTime(rs.getTimestamp("STARTTIME"));
				dto.setEndTime(rs.getTimestamp("ENDTIME"));
				dto.setAuctionDeadline(rs.getInt("AUCTIONDEADLINE"));
				dto.setBidType(rs.getInt("BIDTYPE"));
				dto.setBidStatus(rs.getInt("BIDSTATUS"));
				dto.setBidType(rs.getInt("BIDTYPE"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return dto;
	}
	/***
	 * 批量更新竞价状态
	 */
	@Override
	public void updateBidStatus(int id,int status) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("UPDATE ");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			sql.append(" SET ");
			sql.append(" BIDSTATUS=?");	
			sql.append(" WHERE ");
			sql.append(" ID=? ");
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, status);
			ps.setInt(2, id);
			ps.executeUpdate();
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
	}
	
	/**
     * 查看当前是否有待竞价、竞价中的车辆
     * @return
     * @throws TmsDaoException
     */
	@Override
	public String getManaId(String serial) throws TmsDaoException {
		
		PreparedStatement ps = null;
		ResultSet rs = null;
        String ManaId="";
		try {
			conn = super.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT  ");
			sql.append("SERIAL  ");
			sql.append("FROM     ");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			sql.append("  WHERE SERIAL =?  ");
			sql.append("  AND BIDSTATUS<? ");
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, serial);
			ps.setInt(2, 3);
			// 执行sql
			rs = ps.executeQuery();
			while (rs.next()) {
				ManaId=rs.getString("SERIAL");
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return ManaId;
	}

	/**
     * 竞价管理表最新ID取得
     * @return
     * @throws TmsDaoException
     */
	@Override
	public int getManaMaxId() throws TmsDaoException {
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		int maxId = 0;
		try {
			conn = super.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT MAX(ID) FROM  ");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			ps = conn.prepareStatement(sql.toString());

			// 执行sql
			rs = ps.executeQuery();
			while (rs.next()) {
				maxId=rs.getInt("MAX(ID)");
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return maxId;
	}
	
	/**
     * 更新出价商户总数量
     * @return
     * @throws TmsDaoException
     */
	@Override
	public void updateExpetPricesNum(int id) throws TmsDaoException {
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("    UPDATE    ");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			sql.append("  SET ");
			sql.append("ExpetPricesNum= ifnull(ExpetPricesNum,0) + 1");
			sql.append(" WHERE ");
			sql.append("ID=?");
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
	}
	/**
     * 更新最高出价，最高出价商户id，出价商户总数量
     * @return
     * @throws TmsDaoException
     */
	@Override
	public void updateFinalExpetPricesAndExpetPricesNum(AucManagerDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("    UPDATE    ");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			sql.append("  SET        ");
			sql.append("	finalExpetPrices=?,");
			sql.append("	memberId=?,");
			sql.append("	ExpetPricesNum = ifnull(ExpetPricesNum,0) + 1");
			sql.append(" WHERE ");
			sql.append("	ID=?");
			ps = conn.prepareStatement(sql.toString());

			ps.setString(1, dto.getFinalExpetPrices());
			ps.setString(2, dto.getMemberId());
			ps.setInt(3, dto.getId());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
	}
	/**
     * 查询当前车辆的最高出价
     * @return
     * @throws TmsDaoException
     */
	@Override
	public int selectFinalExpetPricesById(int id) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		int finalExpetPrices = 0;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT finalExpetPrices FROM  ");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			sql.append(" WHERE id = ?");
			ps = conn.prepareStatement(sql.toString());

			ps.setInt(1, id);
			// 执行sql
			rs = ps.executeQuery();
			while (rs.next()) {
				finalExpetPrices=rs.getInt("finalExpetPrices");
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
		return finalExpetPrices;
	}
	
	/**
     * 更新tms_auc_management表BidStatus
     * @return
     * @throws TmsDaoException
     */
	@Override
	public void updateBistatusById(AucManagerDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("UPDATE ");
			sql.append(TmsTableNames.TMS_AUC_MANAGEMENT);
			sql.append("  SET bidStatus=? ");
			sql.append(" WHERE ");
			sql.append(" ID=?");
			ps = conn.prepareStatement(sql.toString());

			ps.setInt(1, dto.getBidStatus());
			ps.setInt(2, dto.getId());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
		
	}
	
}
